﻿using InvestmentIntelligence.DB;
using InvestmentIntelligence.DbModel.Models;
using InvestmentIntelligence.Logging.Core;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class ExcessTimeSerieRepository:ISqlExcessTimeSerieRepository
    {
        //todo: remove this
        #region temporary

        public static readonly Dictionary<string, TimeSpan> Times = new Dictionary<string, TimeSpan>()
                                                            {
                                                                {"RANGE", new TimeSpan()},
                                                                {"BACKWARD", new TimeSpan()},
                                                                {"FORWARD", new TimeSpan()},
                                                            };
        public static readonly Dictionary<string, int> Counts = new Dictionary<string, int>()
                                                            {
                                                                {"RANGE", 0},
                                                                {"BACKWARD", 0},
                                                                {"FORWARD", 0},
                                                            };

        private static readonly object Lock = new object();

        public static void LogStats()
        {
            lock (Lock)
            {
                Log.Info("Excess Time Series reading stats:");
                foreach (var kv in Times)
                {
                    Log.Info(kv.Key + " : " + Counts[kv.Key] + " reads in " + kv.Value);
                }
            }
        }

        #endregion

        public List<ExcessTimeSerieAdbInfo> GetAdbInfos(int securityId, int fundBookId, DateTime startDate, DateTime endDate)
        {
            var timer = new Stopwatch();
            timer.Start();

            using (var dc = new DataConnection())
            {
                const string sql = @"
SELECT [ExcessReturn],[PreviousUnitPosition],[Sigma],[Date],[Beta]
FROM  [TradingData].[BookSecurityToBenchmark] AS bsb
INNER JOIN [TradingData].[ExcessTimeSerie] AS ets 
ON (bsb.[SecurityId] = ets.[SecurityId]) AND (bsb.[BenchmarkId] = ets.[BenchmarkId])
WHERE (bsb.[FundBookEntityId] = @bId) AND (ets.[Date] >= @d1) AND (ets.[Date] <= @d2) AND (@sId = ets.[SecurityId])
";
                dc.Parameters.AddWithValue("@bId", fundBookId);
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@d1", startDate);
                dc.Parameters.AddWithValue("@d2", endDate);
                var data = dc.Query<ExcessTimeSerieAdbInfo>(sql).ToList();
                timer.Stop();
                lock (Lock)
                {
                    Times["RANGE"] += timer.Elapsed;
                    Counts["RANGE"]++;
                }
                return data;
            }
        }

        public List<ExcessTimeSerieAdbInfo> EnumerateBackward(int securityId, int fundBookId, DateTime fromDate)
        {
            var timer = new Stopwatch();
            timer.Start();

            using (var dc = new DataConnection())
            {
                var sql = @"
SELECT [ExcessReturn],[PreviousUnitPosition],[Sigma],[Date],[Beta]
FROM  [TradingData].[BookSecurityToBenchmark] AS bsb
INNER JOIN [TradingData].[ExcessTimeSerie] AS ets 
ON (bsb.[SecurityId] = ets.[SecurityId]) AND (bsb.[BenchmarkId] = ets.[BenchmarkId])
WHERE (bsb.[FundBookEntityId] = @bId) AND (ets.[Date] < @date) AND (@sId = ets.[SecurityId])
ORDER BY ets.[Date] DESC
";
                dc.Parameters.AddWithValue("@bId", fundBookId);
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@date", fromDate);
                var data = dc.Query<ExcessTimeSerieAdbInfo>(sql).ToList();
                timer.Stop();
                lock (Lock)
                {
                    Times["BACKWARD"] += timer.Elapsed;
                    Counts["BACKWARD"]++;
                }
                return data;
            }
        }

        public List<ExcessTimeSerieAdbInfo> EnumerateForward(int securityId, int fundBookId, DateTime fromDate)
        {
            var timer = new Stopwatch();
            timer.Start();

            using (var dc = new DataConnection())
            {
                var sql = @"
SELECT [ExcessReturn],[PreviousUnitPosition],[Sigma],[Date],[Beta]
FROM  [TradingData].[BookSecurityToBenchmark] AS bsb
INNER JOIN [TradingData].[ExcessTimeSerie] AS ets 
ON (bsb.[SecurityId] = ets.[SecurityId]) AND (bsb.[BenchmarkId] = ets.[BenchmarkId])
WHERE (bsb.[FundBookEntityId] = @bId) AND (ets.[Date] > @date) AND (@sId = ets.[SecurityId])
ORDER BY ets.[Date]
";
                dc.Parameters.AddWithValue("@bId", fundBookId);
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@date", fromDate);
                var data = dc.Query<ExcessTimeSerieAdbInfo>(sql).ToList();
                timer.Stop();
                lock (Lock)
                {
                    Times["FORWARD"] += timer.Elapsed;
                    Counts["FORWARD"]++;
                }
                return data;
            }
        }

        public ExcessTimeSerieAdbInfo GetPrevAdbInfo(int securityId, int fundBookId, DateTime date)
        {
            using (var dc = new DataConnection())
            {
                var maxDate = QueryMaxDate(dc, securityId, fundBookId, date);
                if (maxDate == null)
                {
                    return null;
                }

                const string sql = @"
SELECT [ExcessReturn],[PreviousUnitPosition],[Sigma],[Date],[Beta]
FROM  [TradingData].[BookSecurityToBenchmark] AS bsb
INNER JOIN [TradingData].[ExcessTimeSerie] AS ets 
ON (bsb.[SecurityId] = ets.[SecurityId]) AND (bsb.[BenchmarkId] = ets.[BenchmarkId])
WHERE (bsb.[FundBookEntityId] = @bId) AND (ets.[Date] = @d) AND (@sId = ets.[SecurityId])
";
                dc.Parameters.AddWithValue("@bId", fundBookId);
                dc.Parameters.AddWithValue("@sId", securityId);
                dc.Parameters.AddWithValue("@d", maxDate.Value);
                return dc.QuerySingle<ExcessTimeSerieAdbInfo>(sql);
            }
        }

        private DateTime? QueryMaxDate(DataConnection dc, int securityId, int fundBookId, DateTime date)
        {
            const string getMaxDateSql = @"
SELECT MAX(ets.[Date])
FROM  [TradingData].[BookSecurityToBenchmark] AS bsb
INNER JOIN [TradingData].[ExcessTimeSerie] AS ets 
ON bsb.[SecurityId] = ets.[SecurityId] AND bsb.[BenchmarkId] = ets.[BenchmarkId]
WHERE (bsb.[FundBookEntityId] = @bId) AND ets.[Date] < @d AND @sId = ets.[SecurityId]
";
            dc.Parameters.AddWithValue("@bId", fundBookId);
            dc.Parameters.AddWithValue("@sId", securityId);
            dc.Parameters.AddWithValue("@d", date);
            return dc.QuerySingle<DateTime?>(getMaxDateSql);
        }

        public List<TraceReturnAdbInfo> GetExcessReturns(int securityId, int benchmarkId, DateTime eventDate, int priorWindow, int postWindow)
        {
            using (var dc = new DataConnection())
            {
                const string cmd = @"
SELECT a.[Date], a.[Return]
FROM 
(
	SELECT TOP {0} [Date], ExcessReturn AS 'Return'
	FROM TradingData.ExcessTimeSerie
	WHERE [Date] < @EventDate AND SecurityId = @SecurityId AND BenchmarkId = @BenchmarkId
    ORDER BY [Date] DESC
) a

UNION

SELECT b.[Date], b.[Return]
FROM 
(
	SELECT TOP {1} [Date], ExcessReturn AS 'Return'
	FROM TradingData.ExcessTimeSerie
	WHERE [Date] > @EventDate AND SecurityId = @SecurityId AND BenchmarkId = @BenchmarkId
    ORDER BY [Date] ASC
) b";

                dc.Parameters.AddWithValue("@SecurityId", securityId);
                dc.Parameters.AddWithValue("@BenchmarkId", benchmarkId);
                dc.Parameters.AddWithValue("@EventDate", eventDate);

                var sqlQuery = string.Format(cmd, priorWindow, postWindow);
                return dc.Query<TraceReturnAdbInfo>(sqlQuery).OrderBy(item => item.Date).ToList();
            }
        }
    }
}
